<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AlterStaffTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('staff', function (Blueprint $table) {

            if(!Schema::hasColumn('staff','latest_in_date')){
                $table->date('latest_in_date')->after('status')->nullable();
            }
            if(!Schema::hasColumn('staff','age')){
                $table->unsignedTinyInteger('age')->after('sex')->nullable();
            }
        });
        $sql =<<<EOF
update staff stf inner join staff_dorm_adjusts sda on stf.id=sda.staff_id
set stf.latest_in_date=sda.adjust_date
where sda.dorm_id_from is null;
EOF;


        DB::unprepared($sql);
        $sql =<<<EOF
update staff stf inner join staff_dorm_adjusts sda on stf.id=sda.staff_id 
set stf.latest_in_date=null
where sda.dorm_id_to is null;
EOF;


        DB::unprepared($sql);
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('staff', function (Blueprint $table) {

            if(Schema::hasColumn('staff','latest_in_date')){
                $table->dropColumn('latest_in_date');
            }

            if(Schema::hasColumn('staff','age')){
                $table->dropColumn('age');
            }

        });
    }
}
